﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using Paros.DAL;
using Paros.Domain;
using System.Windows.Forms;
using Paros.Utils;
namespace Paros.BLL
{
    public class CategoryMgr
    {
        const string SqlGetAllCategories = "SELECT * FROM Categories";
        const string SqlUpdateCategory = "UPDATE Categories SET Name=@name WHERE ID=@id;SELECT @id";
        const string SqlUpdateCategoryParent = "UPDATE Categories SET Parent=@pid WHERE ID=@id;SELECT @id";
        const string SqlSelectCategory = "SELECT * FROM Categories WHERE ID=@id";
        const string SqlSelectCategoriesByParent = "SELECT * FROM Categories WHERE Parent=@pid";
        const string SqlInsertCategory = "INSERT INTO Categories(Name, Parent) VALUES(@name, @parent);SELECT last_insert_rowid() AS RecordID";
        const string SqlDeleteCategory = "DELETE FROM Categories WHERE ID=@id OR Parent=@id";
        DBFactory factory;
        public CategoryMgr()
        {
            factory = new DBFactory();
        }

        public CategoryItem GetItem(DataRow row)
        {
            CategoryItem item = new CategoryItem();
            if (row == null)
                return item;
            item.CategoryName = row["Name"].ToString();
            item.CategoryID = Convert.ToInt32(row["ID"]);
            item.CategoryDescription = row["Description"].ToString();
            item.Count = Convert.ToInt32(row["count"]);
            if (row["Parent"] != null && !row["Parent"].Equals(DBNull.Value))
                item.CategoryParent = Convert.ToInt32(row["Parent"]);

            return item;
        }

        public DataTable GetCategoriesByParentId(int parentId)
        {
            DbParameter[] paras = { DBFactory.Instance.CreateParameter() };
            paras[0].ParameterName = "@pid";
            paras[0].Value = parentId;
            DataSet ds = factory.ExecuteDataSet(SqlSelectCategoriesByParent, paras);
            DataTable table = ds.Tables[0];
            DataRow row = table.NewRow();
            row["ID"] = parentId;
            row["Parent"] = DBNull.Value;
            table.Rows.Add(row);
            try
            {
                ds.Relations.Add("parent-child", table.Columns["ID"], table.Columns["Parent"]);
            }
            catch
            {

                MessageHelper.ShowError("Category data are incorrect.");
            }
            return table;
        }

        public DataTable GetAllCategories()
        {
            DataSet ds = factory.ExecuteDataSet(SqlGetAllCategories, null);
            DataTable table = ds.Tables[0];
            try
            {
                ds.Relations.Add("parent-child", table.Columns["ID"], table.Columns["Parent"]);
            }
            catch
            {
                
                MessageHelper.ShowError("Category data are incorrect.");
            }
            return table;
        }

        public DataRow GetCategory(int id)
        {
            DataRow row = null;
            DataTable table = new DataTable();
            table.Columns.Add("ID");
            table.Columns.Add("Name");
            table.Columns.Add("Parent");
            table.Columns.Add("Description");
            table.Columns.Add("Count");
            DbParameter[] paras = { DBFactory.Instance.CreateParameter()};
            paras[0].ParameterName = "@id";
            paras[0].Value = id;
            using (DbDataReader reader = factory.ExecuteReader(SqlSelectCategory, paras))
            {
                while (reader.Read())
                {
                    row = table.NewRow();
                    row[0] = reader["ID"];
                    row[1] = reader["Name"];
                    row[2] = reader["Parent"];
                    row[3] = reader["Description"];
                    row[4] = reader["Count"];
                }
            }
            return row;
            
        }

        public CategoryItem GetItem(int id)
        {
            CategoryItem item = new CategoryItem();
            DbParameter[] paras = { DBFactory.Instance.CreateParameter() };
            paras[0].ParameterName = "@id";
            paras[0].Value = id;
            using (DbDataReader reader = factory.ExecuteReader(SqlSelectCategory, paras))
            {
                while (reader.Read())
                {
                    item.CategoryID = Convert.ToInt32(reader["ID"]);
                    item.CategoryName = reader["Name"].ToString();
                    item.CategoryParent = reader["Parent"] == DBNull.Value ? -1 : Convert.ToInt32(reader["Parent"]);
                    item.CategoryDescription = reader["Description"].ToString();
                    item.Count = Convert.ToInt32(reader["Count"]);
                }
            }
            return item;
        }

        /// <summary>
        /// INsert a new category
        /// </summary>
        /// <param name="parentId"></param>
        /// <param name="name"></param>
        /// <returns>Return new category ID if success</returns>
        public int InsertCategory(int parentId, string name)
        {
            int res = -1;

            DbParameter[] paras = { DBFactory.Instance.CreateParameter(), 
                                              DBFactory.Instance.CreateParameter()};
    
            paras[0].ParameterName = "@name";
            paras[0].Value = name;

            paras[1].ParameterName = "@parent";
            paras[1].Value = parentId;

            res = Convert.ToInt32(factory.ExecuteScalar(SqlInsertCategory, paras));
            return res;
        }

        public int UpdateCategory(int id, int pid)
        {
            int res = -1;
            //the node text hasn't been changed
            DbParameter[] paras = { DBFactory.Instance.CreateParameter(), 
                                              DBFactory.Instance.CreateParameter()};

            paras[0].ParameterName = "@id";
            paras[0].Value = id;

            paras[1].ParameterName = "@pid";
            paras[1].Value = pid;

            res = Convert.ToInt32(factory.ExecuteScalar(SqlUpdateCategoryParent, paras));

            return res;
        }

        public int UpdateCategory(TreeNode node, string newValue)
        {
            int res = -1;
            //the node text hasn't been changed
            DbParameter[] paras = { DBFactory.Instance.CreateParameter(), 
                                              DBFactory.Instance.CreateParameter()};
            if (node != null)
            {
                if (newValue == null)
                    newValue = node.Text;
                
                if (node.Tag == null)
                { 
                    //add node
                    CategoryItem parentItem = node.Parent.Tag as CategoryItem;
                    paras[0].ParameterName = "@name";
                    paras[0].Value = newValue;

                    paras[1].ParameterName = "@parent";
                    paras[1].Value = parentItem.CategoryID;
                    
                    res = Convert.ToInt32(factory.ExecuteScalar(SqlInsertCategory, paras));
                }
                else if (node.Tag != null && node.Text != newValue)
                {
                    //edit node
                    CategoryItem item = node.Tag as CategoryItem;
                    paras[0].ParameterName = "@name";
                    paras[0].Value = newValue;

                    paras[1].ParameterName = "@id";
                    paras[1].Value = item.CategoryID;

                    res = Convert.ToInt32(factory.ExecuteScalar(SqlUpdateCategory, paras));
                }
                
            }
            return res;
        }

        public void DeleteCategory(TreeNode node, bool deleteRecursive)
        {
            CategoryItem item = node.Tag as CategoryItem;

            DbParameter[] paras = { DBFactory.Instance.CreateParameter()};
            paras[0].ParameterName = "@id";
            paras[0].Value = item.CategoryID;


            factory.ExecuteNonQuery(SqlDeleteCategory, paras);

            if(deleteRecursive)
            {
                foreach(TreeNode child in node.Nodes)
                {
                    DeleteCategory(child, true);
                }
            }
        }
    }
}
